﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InHD.model;
using InHD.utilities;
using System.Data;
using System.Collections.ObjectModel;
using System.Data.SqlClient;

namespace InHD.dataaccess
{
    class TransactionRepository
    {
        #region Fields

        readonly List<Transaction> _transactions;
        //For DB Utils
        private DBUltils _db;
        string[] _paramName;
        string[] _value;
        DBUltils.ParamType[] _paramType;

        #endregion // Fields

        #region Constructor

        /// <summary>
        /// Creates a new repository of Transactions.
        /// </summary>
        public TransactionRepository()
        {
            _transactions = new List<Transaction>();
            this._db = new DBUltils();
            if (!_db.connectDB(Constants.Security.DefaultDBUser, Constants.Security.DefaultDBPassword))
                throw new ArgumentNullException(Constants.Messages.CannotConnectDB);
        }

        #endregion // Constructor

        #region Public Interface

        /// <summary>
        /// Raised when a Transaction is placed into the repository.
        /// </summary>
        public event EventHandler<TransactionAddedEventArgs> TransactionAdded;

        /// <summary>
        /// Places the specified Transaction into the repository.
        /// If the Transaction is already in the repository, an
        /// exception is not thrown.
        /// </summary>
        public string AddTransaction(Transaction transaction, ObservableCollection<Package> packageList, int multiply)
        {
            String strConnect = "Data Source=" + Constants.Security.DefaultServer + "; Network Library=DBMSSOCN; Initial Catalog=inhd;User ID=" + Constants.Security.DefaultDBUser + ";Password=" + Constants.Security.DefaultDBPassword + ";";
            SqlConnection myConnection = new SqlConnection(strConnect);
            SqlTransaction myTransaction = null;
            string[] myParamName;
            object[] myParamValue;
            try
            {
                myConnection.Open();
                myTransaction = myConnection.BeginTransaction();
                myParamName = new string[] {"@transactionUNK", "@modified", "@taxUNK", "@discountAmt", "@totalSaleAmt", "@invoiceUNK", "@transType",
                                            "@paymentType", "@accUNK", "@cusUNK", "@staffUNK", "@status", "@customerFName", "@customerLName", "@taxName", "@taxPercentage", "@archiveFlag"};
                myParamValue = new object[] {transaction.TransactionUNK, transaction.Modified, transaction.TaxUNK, transaction.DiscountAmount, transaction.TotalSaleAmount, transaction.InvoiceTemplateUnk, Convert.ToInt16(transaction.TransactionType).ToString(),
                                            Convert.ToInt16(transaction.PaymentType).ToString(), transaction.AccountUNK, transaction.CustomerUnk, transaction.StaffUnk, transaction.Status.ToString(), transaction.CustomerFirstName,
                                            transaction.CustomerLastName, transaction.TaxName, transaction.TaxPercentage, transaction.ArchiveFlag};
                DBUltils.RunStoreProcedure("AddTransaction", myParamName, myParamValue, myConnection, myTransaction);

                foreach (Package package in packageList)
                {
                    myParamName = new string[] { "@packageUNK", "@quantity", "@salePrice", "@transactionUNK", "@productUNK", "@productCode", "@productName", "@multiply" };

                    myParamValue = new string[] { "pack" + Guid.NewGuid().ToString(), package.Quantity.ToString(), package.UnitPrice.ToString(), transaction.TransactionUNK,
                                                package.ProductUNK, package.SerialNo, package.ProductName, multiply.ToString()};
                    DBUltils.RunStoreProcedure("[AddPackage]", myParamName, myParamValue, myConnection, myTransaction);
                }

                
                myTransaction.Commit();

                if (multiply > 0) ////sale 
                {
                    if (!string.IsNullOrEmpty(transaction.ArchiveFlag)) ////Paynow 
                    {
                        if (transaction.PaymentType.Equals(PaymentTypes.Cash))
                        {
                            UpdateAccountRelated("111", 0, transaction.TotalSaleAmount);
                            UpdateAccountRelated("156", transaction.TotalSaleAmount, 0);
                        }
                        else if (transaction.PaymentType.Equals(PaymentTypes.Transit))
                        {
                            UpdateAccountRelated("112", 0, transaction.TotalSaleAmount);
                            UpdateAccountRelated("156", transaction.TotalSaleAmount, 0);
                        }
                    }
                    else
                    {
                        UpdateAccountRelated("131", 0, transaction.TotalSaleAmount);
                        UpdateAccountRelated("156", transaction.TotalSaleAmount, 0);
                    }
                }
                else  ////recevie goods
                {
                    if (!string.IsNullOrEmpty(transaction.ArchiveFlag)) ////Paynow 
                    {
                        if (transaction.PaymentType.Equals(PaymentTypes.Cash))
                        {
                            UpdateAccountRelated("156", 0, transaction.TotalSaleAmount);
                            UpdateAccountRelated("111", transaction.TotalSaleAmount, 0);
                        }
                        else if (transaction.PaymentType.Equals(PaymentTypes.Transit))
                        {
                            UpdateAccountRelated("156", 0, transaction.TotalSaleAmount);
                            UpdateAccountRelated("112", transaction.TotalSaleAmount, 0);
                        }
                    }
                    else
                    {
                        UpdateAccountRelated("156", 0, transaction.TotalSaleAmount);
                        UpdateAccountRelated("331", transaction.TotalSaleAmount, 0);
                    }
                }
            }
            catch (SqlException ex)
            {
                myTransaction.Rollback();
                Logger.RecordMessage(ex, MessageType.Error);
                return ex.Message;
            }
            finally
            {
                myConnection.Close();
            }
            return string.Empty;
        }

        public string EditTransaction(Transaction transaction)
        {
            try
            {
                if (transaction == null)
                    throw new ArgumentNullException("transaction");

                //// edit existing product which have customerUNK  == customer.customerUNK
                if (transaction.TransactionUNK.Equals(transaction.TransactionUNK))
                {
                    if (_db == null)
                        throw new ArgumentNullException(Constants.Messages.DBNotInitialized);
                    _paramName = new string[]{"@transactionUNK",
                                        "@modified",
                                        "@taxUNK",
                                        "@discountAmt",
                                        "@totalSaleAmt",
                                        "@invoiceUNK",
                                        "@transType",
                                        "@paymentType",
                                        "@accUNK",
                                        "@cusUNK",
                                        "@staffUNK",
                                        "@status",
                                        "@customerFName",
                                        "@customerLName",
                                        "@taxName",
                                        "@taxPercentage"};
                    _paramType = new DBUltils.ParamType[]{DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.DateTime,
                                                    DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Real,
                                                    DBUltils.ParamType.Real,
                                                    DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Int,
                                                    DBUltils.ParamType.Int,
                                                    DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Int,
                                                    DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Real
                                                    };
                    _value = new string[]{transaction.TransactionUNK,
                                    transaction.Modified.ToString(),
                                    transaction.TaxUNK,
                                    transaction.DiscountAmount.ToString(),
                                    transaction.TotalSaleAmount.ToString(),
                                    transaction.InvoiceTemplateUnk,
                                    Convert.ToInt16(transaction.TransactionType).ToString(),
                                    Convert.ToInt16(transaction.PaymentType).ToString(),
                                    transaction.AccountUNK,
                                    transaction.CustomerUnk,
                                    transaction.StaffUnk,
                                    transaction.Status.ToString(),
                                    transaction.CustomerFirstName,
                                    transaction.CustomerLastName,
                                    transaction.TaxName,
                                    transaction.TaxPercentage.ToString()
                                    };

                    _db.queryDB("UpdateTransaction", _paramName, _paramType, _value, DBUltils.QueryType.Update);

                    if (!transaction.CustomerLastName.Contains("supplier"))  ////is receivable
                    {
                        if (transaction.PaymentType.Equals(PaymentTypes.Cash))
                        {
                            UpdateAccountRelated("111", 0, transaction.TotalSaleAmount);
                            UpdateAccountRelated("131", transaction.TotalSaleAmount, 0);
                        }
                        else if (transaction.PaymentType.Equals(PaymentTypes.Transit))
                        {
                            UpdateAccountRelated("112", 0, transaction.TotalSaleAmount);
                            UpdateAccountRelated("131", transaction.TotalSaleAmount, 0);
                        }
                    }
                    else
                    {
                        if (transaction.PaymentType.Equals(PaymentTypes.Cash))
                        {
                            UpdateAccountRelated("331", 0, transaction.TotalSaleAmount);
                            UpdateAccountRelated("111", transaction.TotalSaleAmount, 0);
                        }
                        else if (transaction.PaymentType.Equals(PaymentTypes.Transit))
                        {
                            UpdateAccountRelated("331", 0, transaction.TotalSaleAmount);
                            UpdateAccountRelated("112", transaction.TotalSaleAmount, 0);
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                return ex.Message;
            }
            //System.Windows.MessageBox.Show("Edit transaction successfully!!!");
            return string.Empty;
        }

        public string DeleteTransaction(string transUNK)
        {
            try
            {
                if (_db == null)
                    throw new ArgumentNullException(Constants.Messages.DBNotInitialized);
                if (string.IsNullOrEmpty(transUNK))
                    throw new ArgumentNullException("Null Unique key");
                _paramName = new string[] { "@transUNK" };
                _paramType = new DBUltils.ParamType[] { DBUltils.ParamType.Str };
                _value = new string[] { transUNK };

                _db.queryDB("DeleteTransaction", _paramName, _paramType, _value, DBUltils.QueryType.Delete);
            }
            catch (Exception e)
            {
                Logger.RecordMessage(e, MessageType.Error);
                return e.Message;
            }
            //System.Windows.MessageBox.Show("Delete Transaction successfully!!!");
            return string.Empty;
        }

        /// <summary>
        /// Returns a shallow-copied list of all Transactions in the repository.
        /// </summary>
        public List<Transaction> GetTransactions()
        {
            return new List<Transaction>(_transactions);
        }

        public DataTable GetTransactionByStaff(string staffUNK)
        {
            DataSet ds;
            DataTable dt = new DataTable();
            if (_db == null)
                return dt;
            if (staffUNK == null)
                return dt;
            _paramName = new string[]{"@staffUNK"};
            _paramType = new DBUltils.ParamType[]{DBUltils.ParamType.Str};
            _value = new string[]{staffUNK};

            ds = _db.queryDB("GetTransactionByStaff", _paramName, _paramType, _value, DBUltils.QueryType.Select);
            return ds.Tables[0];
        }
        
        public List<Transaction> GetTransactionByCustomer(string cusUNK)
        {
            DataSet ds;
            List < Transaction >  transactions = new List<Transaction>();
            if (_db == null)
                return transactions;
            if (cusUNK == null)
                return transactions;
            _paramName = new string[] { "@cusUNK" };
            _paramType = new DBUltils.ParamType[] { DBUltils.ParamType.Str };
            _value = new string[] { cusUNK };

            ds = _db.queryDB("GetTransactionByCustomer", _paramName, _paramType, _value, DBUltils.QueryType.Select);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Transaction tran = Transaction.CreateTransaction(Convert.ToDateTime(dr["modified"]),
                                                                Convert.ToString(dr["taxUNK"]),
                                                                Convert.ToDouble(dr["discountAmt"]),
                                                                Convert.ToDouble(dr["totalSaleAmt"]),
                                                                Convert.ToString(dr["invTemplUNK"]),
                                                                (TransTypes)Convert.ToInt16(dr["transType"]),
                                                                Convert.ToString(dr["accUNK"]),
                                                                Convert.ToString(dr["cusUNK"]),
                                                                Convert.ToString(dr["staffUNK"]),
                                                                Convert.ToInt16(dr["status"]),
                                                                Convert.ToString(dr["customerFName"]),
                                                                Convert.ToString(dr["customerLName"]),
                                                                Convert.ToString(dr["taxName"]),
                                                                Convert.ToDouble(dr["taxPercentage"]),
                                                                (PaymentTypes)Convert.ToInt16(dr["paymentType"])
                                                                );
                tran.TransactionUNK = Convert.ToString(dr["transactionUNK"]);
                transactions.Add(tran);
            }
            return transactions;
        }

        public DataTable GetPurchaseProductsByCustomer(string cusUNK)
        {
            DataSet ds;
            DataTable dt = new DataTable();
            if (_db == null)
                return dt;
            if (cusUNK == null)
                return dt;
            _paramName = new string[] { "@cusUNK" };
            _paramType = new DBUltils.ParamType[] { DBUltils.ParamType.Str };
            _value = new string[] { cusUNK };

            ds = _db.queryDB("GetPurchaseProductsByCustomer", _paramName, _paramType, _value, DBUltils.QueryType.Select);
            return ds.Tables[0];
        }

        public string UpdateAccountRelated(string accCode, double tkcAmount, double tknAmount)
        {
            try
            {
                if (_db == null)
                    throw new ArgumentNullException(Constants.Messages.DBNotInitialized);
                _paramName = new string[] { "@accCode", "@tkcAmount", "@tknAmount" };
                _paramType = new DBUltils.ParamType[]{DBUltils.ParamType.Str,
                                                    DBUltils.ParamType.Real,
                                                    DBUltils.ParamType.Real
                                                    };
                _value = new string[] { accCode, tkcAmount.ToString(), tknAmount.ToString() };

                _db.queryDB("UpdateAccount", _paramName, _paramType, _value, DBUltils.QueryType.Update);
                return string.Empty;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        #endregion // Public Interface

        #region Private Helpers

        #endregion // Private Helpers
    }
}
